#!/usr/bin/python3
import pymysql
# 学员信息在线管理
# 定义一个用于存放学员信息的列表变量
stulist=[
    {'name':'zhangsan','age':20,'classid':'python02'},
    {'name':'lisi','age':22,'classid':'python03'},
    {'name':'wangwu','age':25,'classid':'python04'}]

#定义一个连接数据库类
class dbManager:

    mydb = None

    def dbConnect(self):
        # 打开数据库连接
        try:
            self.mydb = pymysql.connect(host="localhost", user="root", password="", db="mydb", charset="utf8")
        except BaseException as e:
            print("数据库打开失败！"+ str(e))
    def insertOper(self,sql):
        cursor = self.mydb.cursor()
        cursor.execute(sql)
        self.mydb.commit()

    def selectOper(self,sql):
        cursor = self.mydb.cursor()
        cursor.execute(sql)
        return cursor.fetchall()
    def closeDb(self):
        self.mydb.close()

#定义一个学生信息的输出函数
def showStu(obj):
    #学生信息的输出函数
    allList = obj.selectOper("select * from stu")
    if len(allList)==0:
        print("========== 没有学员信息可以输出！=============")
        return
    print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format("sid","name","age","classid"))
    print("-"*40)
    for i in range(len(allList)):
        print("|{0:<5}| {1:<10}| {2:<5}| {3:<10}|".format(i+1,allList[i][0],allList[i][1],allList[i][2]))

dbObj = dbManager()

dbObj.dbConnect()
'''
#执行一次初始化数据
for m in stulist:
    tmp ="insert into stu value(\'{0}\',{1},\'{2}\')".format(m['name'],m['age'],m['classid'])
    dbObj.insertOper(tmp)
'''

while True:
    # 输出初始界面
    print("="*12,"学员管理系统","="*14)
    print("{0:1} {1:13} {2:15}".format(" ","1. 查看学员信息","2. 添加学员信息"))
    print("{0:1} {1:13} {2:15}".format(" ","3. 删除学员信息","4. 退出系统"))
    print("="*40)
    key = input("请输入对应的选择：")
    # 根据键盘值，判断并执行对应的操作
    if key == "1":
        print("="*12,"学员信息浏览","="*14)
        showStu(dbObj)
        input("按回车键继续：")
    elif key == "2":
        print("="*12,"学员信息添加","="*14)
        inputName=input("请输入要添加的姓名：")
        inputAge=input("请输入要添加的年龄：")
        inputClassid=input("请输入要添加的班级号：")
        sql = "insert into stu value(\'{0}\',{1},\'{2}\')".format(inputName,int(inputAge),inputClassid)
        dbObj.insertOper(sql)
        showStu(dbObj)
        input("按回车键继续：")
    elif key == "3":
        print("="*12,"学员信息删除","="*14)
        showStu(dbObj)
        stuName = input("请输入你要删除的学员的姓名：")
        sql = "delete from stu where name=\'%s\'" % (stuName)
        dbObj.insertOper(sql)
        showStu(dbObj)
        input("按回车键继续：")
    elif key == "4":
        print("="*12,"再见","="*14)
        dbObj.closeDb()
        break
    else:
        print("======== 无效的键盘输入！ ==========")